package test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

public class ExeclManager {
/**
* list 存放查询返回得结果列表
* sheetname excel模板中sheet得名字
* out 输出流对象
**/
 public void getMainCardExcel(int list,String filepath,String sheetname) throws FileNotFoundException, IOException
 {
//  读取工作簿
  System.out.println(filepath);
  HSSFWorkbook workbook =new HSSFWorkbook(new FileInputStream(filepath));
  try
  {
   
   //读取工作表
   HSSFSheet sheet = workbook.getSheet(sheetname);
   HSSFRow row;
   HSSFCell cell = null;
   int a = 1;
   HSSFCellStyle style = this.getStyle(workbook);
	//该行以前得部分从模板中取得;
   row = sheet.createRow(0);
   
   for(int i =0;i<list;i++)
   {
    cell = row.createCell((short) 0,HSSFCellStyle.ALIGN_CENTER);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);

    cell.setCellValue(i+1);
    cell.setCellStyle(style);
    
    cell = row.createCell((short) 1,HSSFCellStyle.ALIGN_CENTER);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);

    cell.setCellValue(""+i);
    cell.setCellStyle(style);
    
    cell = row.createCell((short) 2,HSSFCellStyle.ALIGN_CENTER);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);

    cell.setCellValue(""+i);
    cell.setCellStyle(style);
    
    cell = row.createCell((short) 3,HSSFCellStyle.ALIGN_CENTER);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);

    cell.setCellValue(""+i);
    cell.setCellStyle(style);
    
    cell = row.createCell((short) 4,HSSFCellStyle.ALIGN_CENTER);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);

    cell.setCellValue(""+i);
    cell.setCellStyle(style);
    
    a++;
   }
   
	// 第六步，将文件存到指定位置
		FileOutputStream fout = new FileOutputStream("d:/test2.xls");
		workbook.write(fout);
		fout.flush();
		fout.close();

  }
  catch(Exception e)
  {
   e.printStackTrace();
  }
 }
  public HSSFCellStyle getStyle(HSSFWorkbook workbook)
 {
//     设置字体;
  HSSFFont font = workbook.createFont();
  //设置字体大小;
  font.setFontHeightInPoints((short)9);
  //设置字体名字;
  font.setFontName("Courier New");
  //font.setItalic(true);
  //font.setStrikeout(true);
//     设置样式;
  HSSFCellStyle style = workbook.createCellStyle();
  //设置底边框;
  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  //设置底边框颜色;
  style.setBottomBorderColor(HSSFColor.BLACK.index);
  //设置左边框;
  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  //设置左边框颜色;
  style.setLeftBorderColor(HSSFColor.BLACK.index);
  //设置右边框;
  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
  //设置右边框颜色;
  style.setRightBorderColor(HSSFColor.BLACK.index);
  //设置顶边框;
  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
  //设置顶边框颜色;
  style.setTopBorderColor(HSSFColor.BLACK.index);
  //在样式用应用设置的字体;
  style.setFont(font);
     //设置自动换行;
  style.setWrapText(false);
  //设置水平对齐的样式为居中对齐;
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  //设置垂直对齐的样式为居中对齐;
  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  return style;
 }
  
  public static void main(String[] args) {
	  ExeclManager e= new ExeclManager();
	  try {
		e.getMainCardExcel(10, "D:\\test.xls", "test");
	} catch (FileNotFoundException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	} catch (IOException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
}
}
